/*
 * 中北大学
 */
package com.zbdx.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zbdx.bean.College;
import com.zbdx.bean.School;
import com.zbdx.dao.SchoolDao;
import com.zbdx.util.DBUtil;

/**
 *
 * @author 王政
 * @version 1.0
 * 
 */
public class SchoolDaoImpl implements SchoolDao{

	//学校登录
	@Override
	public School login(int Schoolid, String SchoolName) {
		String sql= "select * from school where Schoolid=? and schoolName=?";
		Connection con = null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		con=DBUtil.getConnection();
		try {
			ps=con.prepareStatement(sql);
			ps.setInt(1, Schoolid);
			ps.setString(2, SchoolName);
			rs=ps.executeQuery();
			if(rs.next()) {
				int get_id=rs.getInt("Schoolid");
				String get_Name=rs.getString("SchoolName");				
				String get_Pri= rs.getString("SchoolPri");
				String get_Add=rs.getString("SchoolAdd");
				String get_Email=rs.getString("SchoolEmail");
				String get_Tel=rs.getString("schoolTel");
				String get_Website=rs.getString("SchoolWebsite");
				School school = new School(get_id, get_Name, get_Pri, get_Add, get_Email,get_Tel, get_Website);
				return school;
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(rs, ps, con);
		}
		
		
		
		return null;
	}

	//增加学校信息
	@Override
	public boolean addSchool(School school) {
		Connection con=null;
		PreparedStatement ps=null;
		con=DBUtil.getConnection();
		String sql = "insert into school value(?,?,?,?,?,?,?)";
		try {
			ps=con.prepareStatement(sql);
			ps.setInt(1,school.getSchoolid());
			ps.setString(2, school.getSchoolName());
			ps.setString(3, school.getSchoolPri());
			ps.setString(4, school.getSchoolAdd());
			ps.setString(5, school.getSchoolEmail());
			ps.setString(6, school.getSchoolTel());
			ps.setString(7, school.getSchoolWebsite());
			int i = ps.executeUpdate();
            if(i>0) {
				return true;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(null, ps, con);;
		}
		return false;
	}


	//增加学院信息
	@Override
	public boolean addCollege(College college) {
		Connection conn = null;
		PreparedStatement ps=null;
		conn=DBUtil.getConnection();
		String sql = "insert into college value(null,?,?,?,?,?)";
		
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, college.getCollegeName());
			ps.setString(2, college.getCollegeRoom());
			ps.setString(3, college.getCollegeTel());
			ps.setString(4, college.getCollegeEmail());
			ps.setInt(5, college.getCollegeDeanID());
			int i = ps.executeUpdate();
            if(i>0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(null, ps, conn);
		}
		
		
		return false;
	}

	//删除学院
	@Override
	public boolean deleteCollege(int collegeId) {
		Connection conn = null;
		PreparedStatement ps=null;
		conn=DBUtil.getConnection();
		String sql = "delete from college where collegeId=?";
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, collegeId);
			int i = ps.executeUpdate();
			if(i!=0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(null, ps, conn);
		}
		return false;
	}

	//修改学院
	@Override
	public boolean updateCollege(College college) {
		Connection conn = null;
		PreparedStatement ps=null;
		conn=DBUtil.getConnection();
		String sql = "update college set collegeName=?, "
				+ "collegeRoom=?,collegeTel=?,collegeEmail=?,"
				+ "collegeDeanID=? where collegeId=?";
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, college.getCollegeName());
			ps.setString(2, college.getCollegeRoom());
			ps.setString(3, college.getCollegeTel());
			ps.setString(4, college.getCollegeEmail());
			ps.setInt(5, college.getCollegeDeanID());
			ps.setInt(6, college.getCollegeId());
			int i = ps.executeUpdate();
			if(i>0) {
				return true;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(null, ps, conn);
		}
		
		return false;
	}

	//查询学院
	@Override
	public List<College> findcollege() {
		Connection conn = null;
		PreparedStatement ps=null;
		ResultSet rs = null;
		conn=DBUtil.getConnection();
		String sql="select * from college";
		List<College> list = new ArrayList<College>();
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				int college_Id = rs.getInt("collegeId");
				String college_Name = rs.getString("collegeName");
				String college_Room = rs.getString("collegeRoom");
				String college_Tel	= rs.getString("collegeTel");
				String college_Email= rs.getString("collegeEmail");
				int college_DeanID=rs.getInt("collegeDeanID");
				College college = new College();
				college.setCollegeId(college_Id);
				college.setCollegeName(college_Name);
				college.setCollegeRoom(college_Room);
				college.setCollegeTel(college_Tel);
				college.setCollegeEmail(college_Email);
				college.setCollegeDeanID(college_DeanID);
				list.add(college);
				//System.out.println(college);
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(rs, ps, conn);
		}	
		return list;
	}

	
	//修改学院显示
	@Override
	public College findColl(int collegeId) {
		Connection conn = null;
		PreparedStatement ps=null;
		ResultSet rs = null;
		conn = DBUtil.getConnection();
		String sql = "select * from college where collegeId=?";
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, collegeId);
			rs=ps.executeQuery();
			while(rs.next()) {
				int college_Id = rs.getInt("collegeId");
				String college_Name = rs.getString("collegeName");
				String college_Room = rs.getString("collegeRoom");
				String college_Tel	= rs.getString("collegeTel");
				String college_Email= rs.getString("collegeEmail");
				int college_DeanID=rs.getInt("collegeDeanID");
				College college = new College();
				college.setCollegeId(college_Id);
				college.setCollegeName(college_Name);
				college.setCollegeRoom(college_Room);
				college.setCollegeTel(college_Tel);
				college.setCollegeEmail(college_Email);
				college.setCollegeDeanID(college_DeanID);
				return college;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.closeResource(rs, ps, conn);
		}	
		return null;
	}


}
